﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using BOM.entity;

namespace BOM.dao
{
    class BomDaoImp
    {
        //根据ID获取BOM表信息
        public static Bom getBomById(int id )
        {
            String sql = "select * from bom where fid ="+id;
            DataTable ds = DbService.getSQLTable(sql,"bom");
            List<Bom> list = fillBomList(ds);
            if(list!=null && list.Count>0)
                return list[0];
            else
                return null;
        }

        //获根节点BOM
        public static Bom getRootNodeBom()
        {
            String sql = "select * from bom where fparentId is null or fparentId =0 ";
            DataTable ds = DbService.getSQLTable(sql, "bom");
            List<Bom> list = fillBomList(ds);
            if (list != null && list.Count > 0)
                return list[0];
            else
                return null;
        }
        //获取子节点
        public static List<Bom> getChildBomsByParentId(int parentId)
        {
            String sql = "select * from bom where fparentId =" + parentId + " and ftype=1  order by fpartNumber";
            DataTable ds = DbService.getSQLTable(sql, "bom");
            List<Bom> list = fillBomList(ds);
            if (list != null && list.Count > 0)
                return list;
            else
                return null;
        }
        //获取子节点和自身节点
        public static List<Bom> getChildBomsAndSelfByParentId(int parentId)
        {
            String sql = "select * from bom where fid= "+parentId+"or fparentId =" + parentId + "  order by fpartNumber";
            DataTable ds = DbService.getSQLTable(sql, "bom");
            List<Bom> list = fillBomList(ds);
            if (list != null && list.Count > 0)
                return list;
            else
                return null;
        }
        //根据DataTable转换为List形式
        public static List<Bom> fillBomList(DataTable ds)
        {
            if (ds == null || ds.Rows.Count == 0)
                return null;
            List<Bom> list = new List<Bom>();
            BOM.entity.Bom bom = null;
            for (int i = 0; i < ds.Rows.Count;i++ )
            {
                bom = new BOM.entity.Bom();                
                bom.setId((int)ds.Rows[i]["fid"]);
                if (ds.Rows[i]["fparentId"]!= null && !"".Equals(ds.Rows[i]["fparentId"].ToString()))
                    bom.setParentId(Convert.ToInt32(ds.Rows[i]["fparentId"].ToString()));
                if (ds.Rows[i]["fLevel"] != null && !"".Equals(ds.Rows[i]["fLevel"].ToString()))
                    bom.setLevel(Convert.ToInt32(ds.Rows[i]["fLevel"]));
                if (ds.Rows[i]["fisleaf"] != null)
                    bom.setIsLeaf(Boolean.Parse(ds.Rows[i]["fisleaf"].ToString()));
                else
                    bom.setIsLeaf(false);
                if (!"".Equals(ds.Rows[i]["flongNumber"].ToString()))
                    bom.setLongNumber((String)ds.Rows[i]["flongNumber"]);
                if (!ds.Rows[i]["fpartNumber"].ToString().Equals(""))
                    bom.setPartNumber((String)ds.Rows[i]["fpartNumber"]);
                if (!ds.Rows[i]["fpartName"].ToString().Equals(""))
                    bom.setPartName((String)ds.Rows[i]["fpartName"]);
                if (!ds.Rows[i]["fdarawingNo"].ToString().Equals(""))
                    bom.setDarawingNo((String)ds.Rows[i]["fdarawingNo"]);
                if (!ds.Rows[i]["fnaterialCost"].ToString().Equals(""))
                    bom.setNaterialCost(float.Parse(ds.Rows[i]["fnaterialCost"].ToString()));
                if (!ds.Rows[i]["flabourCost"].ToString().Equals(""))
                    bom.setLabourCost(float.Parse(ds.Rows[i]["flabourCost"].ToString()));
                if (!ds.Rows[i]["ftotalCost"].ToString().Equals(""))
                    bom.setTotalCost(float.Parse(ds.Rows[i]["ftotalCost"].ToString()));
                if (!ds.Rows[i]["fdesigner"].ToString().Equals(""))
                    bom.setDesigner((String)ds.Rows[i]["fdesigner"]);
                if (!ds.Rows[i]["fversion"].ToString().Equals(""))
                    bom.setVersion((String)ds.Rows[i]["fversion"]);
                if (!ds.Rows[i]["fcomponent"].ToString().Equals(""))
                    bom.setCompenent((String)ds.Rows[i]["fcomponent"]);
                if (!ds.Rows[i]["fmaterial"].ToString().Equals(""))
                    bom.setMaterial((String)ds.Rows[i]["fmaterial"]);
                if (!ds.Rows[i]["fsupplier1"].ToString().Equals(""))
                    bom.setSupplier1((String)ds.Rows[i]["fsupplier1"]);
                if (!ds.Rows[i]["fsupplier2"].ToString().Equals(""))
                    bom.setSupplier1((String)ds.Rows[i]["fsupplier2"]);
                if (!ds.Rows[i]["fsupplyCycle"].ToString().Equals(""))
                    bom.setSupplyCycle((String)ds.Rows[i]["fsupplyCycle"]);
                    bom.setIsLeaf(Boolean.Parse(ds.Rows[i]["fisleaf"].ToString()));
                if (!ds.Rows[i]["ftype"].ToString().Equals(""))
                        bom.setType(int.Parse(ds.Rows[i]["ftype"].ToString()));
                if (!ds.Rows[i]["fQty"].ToString().Equals(""))
                    bom.setQty(int.Parse(ds.Rows[i]["fQty"].ToString()));
                if (!ds.Rows[i]["fsumCost"].ToString().Equals(""))
                    bom.setSumCost(float.Parse(ds.Rows[i]["fsumCost"].ToString()));
                list.Add(bom);
            }
            return list;
        }
        //SQL查询BOM表信息
        public static List<Bom> getBomListBySQL(string selectSQL)
        {
            DataTable ds =   DbService.getSQLTable(selectSQL, "bom");
            return fillBomList(ds);
        }
        //模糊查询搜索
        public static List<Bom> getSearchResult(String searchContent)
        {
            String sql = "select * from bom where fpartNumber like '%"+searchContent+"%'or Fpartname like '%"+searchContent+"%' order by fpartNumber";
            return getBomListBySQL(sql);
        }
        //添加新的BOM信息节点
        public static int addNew(Bom bom)
        {
            String sql = "insert into bom(fparentid,flevel,flongnumber,fpartNumber,fpartName,fdarawingNo,fnaterialCost,flabourCost,ftotalCost,"
            + "fdesigner,fversion,fcomponent,fmaterial,fsupplier1,fsupplier2,fsupplyCycle,fisLeaf,ftype,fqty,fsumCost ) ";

            String valueStr = bom.getParentId() + "," + bom.getLevel() + ",'" + bom.getLongNumber() + "','" + bom.getPartNumber() + "','"
                + bom.getPartName() + "','" + bom.getDarawingNo() + "'," + bom.getNaterialCost() + "," + bom.getLabourCost() + ","
                + bom.getTotalCost() + ",'" + bom.getDesigner() + "','" + bom.getVersion() + "','" + bom.getComponent() + "','" + bom.getMaterial() + "','"
                + bom.getSupplier1() + "','" + bom.getSupplier2() + "','" + bom.getSupplyCycle() + "',"+(bom.isleaf()?1:0)+","+bom.getType()+","+bom.getQty()+","+bom.getSumCost()+" ";
            sql = sql + "values(" + valueStr + ")";
            return DbService.executeSQL(sql); ;
        }

  /*      public static int save(Bom bom)
        {
            return 0;
        }
        */
        //更新BOM信息
        public static int update(Bom bom)
        {
            String updateSql = "update bom "
            +"set fparentid ="+bom.getParentId()+" , "
            +"flevel ="+bom.getLevel()+"   ,"
            +"flongnumber ='"+bom.getLongNumber()+"'   ,"
            +"fpartNumber ='"+bom.getPartNumber()+"'   ,"
            +"fpartName ='"+bom.getPartName()+"'   ,"
            +"fdarawingNo ='"+bom.getDarawingNo()+"'   ,"
            +"fnaterialCost ="+bom.getNaterialCost()+"   ,"
            +"flabourCost ="+bom.getLabourCost()+"   ,"
            +"ftotalCost ="+bom.getTotalCost()+"   ,"
            +"fdesigner ='"+bom.getDesigner()+"'   ,"
            +"fversion ='"+bom.getVersion()+"'   ,"
            +"fcomponent ='"+bom.getComponent()+"'   ,"
            +"fmaterial ='"+bom.getMaterial()+"'   ,"
            +"fsupplier1 ='"+bom.getSupplier1()+"'   ,"
            +"fsupplier2 ='"+bom.getSupplier2()+"'   ,"
            +"fsupplyCycle ='"+bom.getSupplyCycle()+"',  "
            +"ftype ="+bom.getType()+","
            +"fQty = "+bom.getQty()+","
            +"fsumCost = "+bom.getSumCost()+" ,"
            + "fIsLeaf = " + bom.isleaf() + " "
            +" where fid="+bom.getId();
            return DbService.executeSQL(updateSql);
            
        }
        //删除节点
        public static int remove(Bom bom)
        {
            String sql = "delete * from bom where fid="+bom.getId();
            return DbService.executeSQL(sql);
        }
        //更新当前BOM成本字段
        public static int updateCost(Bom bom)
        {
            //文档
            if (3 == bom.getType())
                return 1;
            //如果是零件节点，更新当前节点即可
            else if (2 == bom.getType())
            {
             return   update(bom);
            }
            //若组件节点，则取下级成本字段值之和更新
            else
             {
/*                 String parentSql = "SELECT sum(fNaterialCost) as naterialCost,sum(flabourCost) as labourCost "
                                  +" FROM bom where fparentId ="+bom.getId();
                 DataTable ds = DbService.getSQLTable(parentSql,"tb");
                 if(ds!=null && ds.Rows.Count>0)
                 {
                     bom.setNaterialCost(float.Parse(ds.Rows[0]["naterialCost"].ToString()));
                     bom.setLabourCost(float.Parse(ds.Rows[0]["labourCost"].ToString()));
                     bom.setTotalCost(bom.getNaterialCost() + bom.getLabourCost());
                 }
 * */
                 String sql = "select fNaterialCost ,flabourCost,fqty,fsumCost From bom where fparentid="+bom.getId()+" and (ftype=1 or ftype=2)";
                 DataTable ds = DbService.getSQLTable(sql,"tb");
                 float naterialCost=0, labourCost = 0, sumCost = 0;
                 int qty = 1 ;
                 for (int i = 0; i < ds.Rows.Count; i++)
                 {
                     if("".Equals(ds.Rows[i]["fqty"].ToString()))
                         qty = 1 ;
                     else
                         qty = int.Parse(ds.Rows[i]["fqty"].ToString());
                     naterialCost =naterialCost+ (qty * float.Parse(ds.Rows[i]["fnaterialCost"].ToString()));
                     labourCost = labourCost + (qty * float.Parse(ds.Rows[i]["flabourCost"].ToString()));
                     sumCost = sumCost + float.Parse(ds.Rows[i]["fsumCost"].ToString());
                 }
                 bom.setNaterialCost(naterialCost);
                 bom.setLabourCost(labourCost);
                 bom.setTotalCost(bom.getNaterialCost()+bom.getLabourCost());
                 int qty1 = 1;
                 if (bom.getQty() == null || bom.getQty() == 0)
                     qty1 = 1;
                 else
                     qty1 = bom.getQty();
                 bom.setQty(qty1);
                 bom.setSumCost(bom.getTotalCost() * qty1);
                     
                 return update(bom);
                
             }
        }
    }
}
